/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cn.com.jsepc.rep.report;

import cn.com.jsepc.rep.comm.ConstUtil;
import cn.com.jsepc.rep.demension.CheckLeaf;
import cn.com.jsepc.rep.vo.FactTableVO;
import cn.com.jsepc.rep.vo.FieldDataVO;
import cn.com.jsepc.rep.vo.RepDataVO;
import cn.com.jsepc.rep.vo.RepDemensionVO;
import com.jarp.db.DAO;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.Vector;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 *
 * @author yuan
 */
public class FillDataDao_Wan {
    public static String DemIsNotSelect="99999";
    
      public String getDataGrid(String start, String limit, String repid,String corpid,String demJsonArr) {
    	String factsql=this.getDataGrid(repid, corpid, demJsonArr);
        String res = "";
        DAO dao = new DAO();
        JSONArray json = new JSONArray();
        String totalCount = null;
        try {
            int iStart = Integer.parseInt(start);
            int iPageSize = Integer.parseInt(limit);
            int iPage = iStart/iPageSize + 1;
            StringBuffer demwhereBuffer = new StringBuffer();
            if (demJsonArr!=null && !demJsonArr.equals("")){
            	JSONObject qryjson = JSONObject.fromString(demJsonArr);
	            JSONArray demArr = qryjson.getJSONArray("demlist");
	            for (int i = 0; i < demArr.length(); i++)
	            {
	            	JSONObject jsonDem = demArr.getJSONObject(i);
	                String facdimtable = jsonDem.getString("facdimtable");
	                String facdimcodecol = jsonDem.getString("facdimcodecol");
	                String facdimvalue = jsonDem.getString("facdimvalue");
	                if (!facdimvalue.equals(this.DemIsNotSelect)){
	                	demwhereBuffer.append("\n and "+facdimtable+"."+facdimcodecol+" in ("+facdimvalue+")");
	                }
	            }
            }else{
            	System.out.println("demJsonArr is null");
            }
            RepDataVO repVo = getRepDemension(repid);
            if (repVo != null) {
            String tablename = repVo.getReffact(); //TF_REP_HRCNT
            String faccol = repVo.getRepvaluecol(); //HTCNT
            String facsumcol = "SUMVAL";
            String dwDimCodeCol = null;
            String dwDimCodeVal = null;
            boolean bDwDim = false;

            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            if (tablename!=null && faccol != null && repDemLst!=null && repDemLst.size()>0) {
            StringBuffer ssql = new StringBuffer();
            StringBuffer ssqlTot = new StringBuffer();
            StringBuffer sdemcol = new StringBuffer();
            StringBuffer sdemtab = new StringBuffer();
            StringBuffer sdemon = new StringBuffer();
            StringBuffer sorder = new StringBuffer();
            ssql.append("select a."+faccol+",a."+facsumcol+",a.YOYRATION,a.MONRATION,a.TYOYRATION,a.TMONRATION,a.PREVAL,a.TPREVAL,a.TSUMVAL,a.TREPVAL");
            for (int j = 0; j < repDemLst.size(); j++) {
                RepDemensionVO repDemVO = repDemLst.get(j);
                String dimtable = repDemVO.getDimtable(); //td_org
                String dimidcol = repDemVO.getIdcol();
                String facdimcol = repDemVO.getFactdimcol();  //ORG_ID
                String dimcodecol = repDemVO.getCodecol();
                String dimnamecol = repDemVO.getNamecol();
                boolean b=this.isSelect(facdimcol, demJsonArr);
                if(b==true){
                ssql.append("\n ,z."+facdimcol);
                ssql.append("\n ,z."+facdimcol+dimnamecol);
                
                
                if (j!=0){
                	sdemtab.append(",");
                	sdemon.append(" and ");
                	sorder.append(",");
                	sdemcol.append("\n,");
                }
                sdemcol.append(""+dimtable+"."+dimidcol +" as "+ facdimcol);
                sdemcol.append("\n,"+dimtable+"."+dimnamecol +" as "+ facdimcol+dimnamecol);
                
                sdemtab.append(dimtable);
                sdemon.append(" z."+facdimcol + " = a."+facdimcol);
                sorder.append(facdimcol);
	            String dimtype = repDemVO.getDemtype();//1单位维度 2时间维度 3其他维度
	            if (dimtype!=null){
	              	if (dimtype.equals("1")){	                	
		                	if (dimcodecol!=null && !dimcodecol.equals("")){
		                		bDwDim = true;
		                		dwDimCodeVal = getDimCodeByID(dimtable,dimidcol,dimcodecol,corpid);
		                		dwDimCodeCol = dimtable+"."+dimcodecol;
		                	}
	              	}
	              }}
                else{
                    ssql.append("\n ,"+DemIsNotSelect+" as "+facdimcol);
                    ssql.append("\n ,"+DemIsNotSelect+" as "+facdimcol+dimnamecol);
                   if (j!=0){
                	sdemon.append(" and ");
                }
                  sdemon.append( " a."+facdimcol+"="+this.DemIsNotSelect);    
                    
                }
            }            
            ssql.append("\n from (select ");    
            sdemcol=this.clear_char(sdemcol);
            sdemtab=this.clear_char(sdemtab);
            sorder=this.clear_char(sorder);
            
                System.out.println("sdemcol="+sdemcol.toString());
            
            ssql.append(sdemcol.toString()); 
            ssql.append("\n from "+sdemtab.toString() + " where 1=1 ");     
            if (corpid!=null && !corpid.equals("") && bDwDim){
            	if (dwDimCodeVal!=null && !dwDimCodeVal.equals("")){
            		ssql.append(" and "+dwDimCodeCol+" like '"+dwDimCodeVal+"%'");
            	}
              
            }
            ssql.append(demwhereBuffer.toString());
            ssql.append("\n ) z ");     
            ssql.append("\n left join "+tablename +" a on "+sdemon);
            ssql.append("\n where a.repval is not null or sumval is not null ");
            ssql.append("\n order by "+sorder.toString());
            
            Vector v = dao.getDataSet(ConstUtil.DBNAMEODS, ssql.toString(),iPage,iPageSize);
            if (v != null) {
               json =  JSONArray.fromObject(v);
            }
            ssqlTot.append("select count(1) CNT from (" + ssql.toString() +" ) zz ");
             totalCount = dao.getDataValue(ConstUtil.DBNAMEODS, ssqlTot.toString());
            }
            }
            res = "{totalCount:" + totalCount + ",data:" + json.toString() + "}";
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return res;
    }
      
      public boolean isSelect(String facdimcol,String demJsonArr){
          DAO dao=new DAO();
          boolean b=true;
          JSONObject qryjson = JSONObject.fromString(demJsonArr);
	            JSONArray demArr = qryjson.getJSONArray("demlist");
	            for (int i = 0; i < demArr.length(); i++)
	            {
	            	JSONObject jsonDem = demArr.getJSONObject(i);
	                String facdimtable = jsonDem.getString("facdimtable");
	                String facdimcodecol = jsonDem.getString("facdimcodecol");
	                String facdimvalue = jsonDem.getString("facdimvalue");
                        String sql="SELECT t.DIMTABLE FROM tb_demension t WHERE t.FACDIMCOL='"+facdimcol+"'";
                        
              try {
                  String value=dao.getDataValue(ConstUtil.DBNAME, sql);
                  if(value.equals(facdimtable)){
                      if ( facdimvalue.equals(this.DemIsNotSelect)){
	                	b=false;
	                }
                  }
              } catch (ServletException ex) {
                  Logger.getLogger(FillDataDao_Wan.class.getName()).log(Level.SEVERE, null, ex);
              }
	                
	            }
          return b;
      }
      
       public String getDataGrid(String repid,String corpid,String demJsonArr) {
       String res = "";
           RepDataVO repVo = getRepDemension(repid);
           if (repVo != null) {
           String tablename = repVo.getReffact(); //TF_REP_HRCNT
           res=tablename;
           FactTableVO.setSql(res);
           }
       
       return res;
   }
       
         public RepDataVO getRepDemension(String repid) {
        RepDataVO repVO = new RepDataVO();
        DAO dao = new DAO();
        try {
            String sql = "select * from ( select * from tb_report where repid=" + repid + " and ISVALID=1 ) a"
                    + " left join tb_rep_demension b on b.repid=a.repid"
                    + " left join tb_demension c on c.demid=b.dimid order by b.dimorder";
            Vector v = dao.getDataSet(ConstUtil.DBNAME, sql);
            if (v != null) {
                ArrayList<RepDemensionVO> repDemLst = new ArrayList<RepDemensionVO>();
                for (int i = 0; i < v.size(); i++) {
                    Hashtable hashRows = (Hashtable) v.get(i);
                    RepDemensionVO repDemVO = new RepDemensionVO();
                    repDemVO.setId((String) hashRows.get("ID"));
                    repDemVO.setRepid((String) hashRows.get("REPID"));
                    repDemVO.setDimid((String) hashRows.get("DIMID"));
                    repDemVO.setHirecdesc((String) hashRows.get("HIRECDESC"));
                    repDemVO.setDemname((String) hashRows.get("DEMNAME"));
                    repDemVO.setFactdimcol(((String) hashRows.get("FACDIMCOL")).toUpperCase());
                    repDemVO.setAvailsname((String) hashRows.get("AVAILSNAME"));
                    repDemVO.setDimorder((String) hashRows.get("DIMORDER"));
                    repDemVO.setDemcode((String) hashRows.get("DEMCODE"));
                    repDemVO.setDimtable((String) hashRows.get("DIMTABLE"));
                    repDemVO.setIdcol(((String) hashRows.get("IDCOL")).toUpperCase());
                    repDemVO.setNamecol(((String) hashRows.get("NAMECOL")).toUpperCase());
                    repDemVO.setParentcol(((String) hashRows.get("PARENTCOL")).toUpperCase());
                    repDemVO.setCodecol(((String) hashRows.get("CODECOL")).toUpperCase());
                    repDemVO.setDemtype((String) hashRows.get("DIMTYPE"));
                    repDemVO.setIsvalid((String) hashRows.get("ISVALID"));
                    repDemVO.setLevelcol((String) hashRows.get("LEVELCOL"));
                    repDemLst.add(repDemVO);
                    if (i == v.size() - 1) {
                        repVO.setRepid((String) hashRows.get("REPID"));
                        repVO.setRepcode((String) hashRows.get("REPCODE"));
                        repVO.setRepname((String) hashRows.get("REPNAME"));
                        repVO.setCataid((String) hashRows.get("CATAID"));
                        repVO.setRepdesc((String) hashRows.get("REPDESC"));
                        repVO.setDesc((String) hashRows.get("CALDESC"));
                        repVO.setDataprec((String) hashRows.get("DATAPREC"));
                        repVO.setDataunit((String) hashRows.get("DATAUNIT"));
                        repVO.setDepartment((String) hashRows.get("DEPARTMENT"));
                        repVO.setSourcetype((String) hashRows.get("SOURCETYPE"));
                        repVO.setDatatype((String) hashRows.get("DATATYPE"));
                        repVO.setUpdatetime((String) hashRows.get("UPDATETIME"));
                        repVO.setReffact((String) hashRows.get("REFFACT"));
                        repVO.setRepvaluecol(((String) hashRows.get("REPVALUECOL")).toUpperCase());
                        repVO.setIssum((String) hashRows.get("ISSUM"));
                        repVO.setIsvalid((String) hashRows.get("ISVALID"));
                        repVO.setRepDemLst(repDemLst);
                    }
                }
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
        return repVO;
    }
         
            public String getDimCodeByID(String dimtablename,String dimidcol,String dimcodecol,String dimidval) {
    	String sret = "";
        try {          
            DAO dao=new DAO();
            String sql="select "+dimcodecol+" from "+dimtablename+" where "+dimidcol+"="+dimidval+"";
            sret=dao.getDataValue(ConstUtil.DBNAMEODS, sql);
            
        } catch (ServletException ex) {
            Logger.getLogger(CheckLeaf.class.getName()).log(Level.SEVERE, null, ex);
            
        }
        return sret;
    }
            
            public StringBuffer clear_char(StringBuffer bstr){
                String temp=bstr.toString();
                temp=temp.trim();
                bstr=new StringBuffer();
                bstr.append(temp);
            while(true){
            if(bstr.charAt(0)==','){
                bstr.deleteCharAt(0);
            }
            else{
                break;
            }
            }
            while(true){
            if(bstr.charAt(bstr.toString().length()-1)==','){
                 bstr.deleteCharAt(bstr.length()-1);
            }
            else{
                break;
            }
            }
            return bstr;
            }
            
             //修改
    public synchronized String DatatbUpdate(String repid,String itemJsonInfo) throws ServletException {
        String ret = "";
        try {
             ArrayList<FieldDataVO> lstResults = new ArrayList<FieldDataVO>();     
            ArrayList<String> sqls = new ArrayList<String>();
            JSONObject jsonObject = new JSONObject(itemJsonInfo);
            DAO dao = new DAO();
            RepDataVO repVo = getRepDemension(repid);
            if (repVo == null) {
                return null;
            }
            ArrayList<RepDemensionVO> repDemLst = repVo.getRepDemLst();
            if (repDemLst == null || repDemLst.size()==0) {
                return null;
            }
            String tablename = repVo.getReffact(); //TF_REP_HRCNT
            String faccol = repVo.getRepvaluecol(); //HTCNT
            String facsumcol = "SUMVAL";
            JSONArray jsonArr = jsonObject.getJSONArray("listData");
            for (int i = 0; i < jsonArr.length(); i++) {
                JSONObject jsonOne = jsonArr.getJSONObject(i);
                StringBuffer strquery = new StringBuffer();
                StringBuffer strupdate = new StringBuffer();
                StringBuffer strupdatewhere = new StringBuffer();
                StringBuffer strinsert = new StringBuffer();
                StringBuffer strinsertval = new StringBuffer();
                String repvalue = jsonOne.getString(faccol);
                if(repvalue==null || repvalue.equals("")){ 
                	repvalue = "NULL";
                }
                String facsumval = jsonOne.getString(facsumcol);
                if(facsumval==null || facsumval.equals("")){ 
                	facsumval = "NULL";
                }
                strquery.append("select "+faccol+" from "+tablename);
                strupdate.append("update " + tablename + " set " + faccol + "=" + repvalue +" ");
                strupdate.append(" ,"+facsumcol+"="+facsumval );
                strupdate.append(" where ");
                strinsert.append("insert into " + tablename +" ( ");
                for (int j = 0; j < repDemLst.size(); j++) {
                    RepDemensionVO repDemVO = repDemLst.get(j);
                    String dimtable = repDemVO.getDimtable(); //td_org
                    String dimidcol = repDemVO.getFactdimcol();  //ORG_ID
                    String colvalue = jsonOne.getString(dimidcol);
                    if (j!=0){
                    	strupdatewhere.append(" and ");
                    	strinsert.append(",");
                    	strinsertval.append(",");
                    }
                    strinsert.append(" "+dimidcol);
                    strupdatewhere.append(" "+dimidcol+" = "+colvalue+"");
                    strinsertval.append(" "+colvalue);
                }
                strquery.append("\n where "+strupdatewhere.toString());
                Vector v = dao.getDataSet(ConstUtil.DBNAMEODS, strquery.toString());
	            if (v != null && v.size()>0) {
	            	strupdate.append(strupdatewhere.toString());
	            	sqls.add(strupdate.toString());
	            }else{
	            	if(repvalue==null || repvalue.equals("")){ 
	                	repvalue = "0";
	                }
	            	if(facsumval==null || facsumval.equals("")){ 
	            		facsumval = "0";
	                }
	            	strinsert.append(" ,"+faccol+","+facsumcol+") values ("+strinsertval.toString()+" ,"+repvalue+","+facsumval+") ");
	            	sqls.add(strinsert.toString());
	            }
            }
            System.out.println("sqls="+sqls.toString());
            if (sqls.size() > 0) {
                if (dao.execCommand(ConstUtil.DBNAMEODS, sqls)) {
                    ret = "ok";
                } else {
                    ret = "fail";
                }
            } else {
                ret = "no";
            }

        } catch (RuntimeException e) {
            e.printStackTrace();
        }
        return ret;
    }
    
}
